﻿USE [ATAChietkhau]
GO

/****** Object:  StoredProcedure [dbo].[sprMayPhaMauReport]    Script Date: 07/28/2010 23:16:43 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sprMayPhaMauReport]
	-- Add the parameters for the stored procedure here
	@BranchID uniqueidentifier, 
			@Month int,
			@Year int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @TrungTamName NVARCHAR(255)
	DECLARE @TrungTamCode NVARCHAR(20)

	SELECT @TrungTamName = BranchName, @TrungTamCode = BranchCode FROM vwMisaBranches WHERE BranchID = @BranchID

	DECLARE @LastDate DATETIME

	SET @LastDate = DATEADD(dd, -1, dbo.Date(@Year, @Month, 1))

	CREATE TABLE #tblMayPhaMauReport
	(
		ID UNIQUEIDENTIFIER,
		TenTrungTam NVARCHAR(255),
		KhachHangID UNIQUEIDENTIFIER,
		MaKH NVARCHAR(100),
		TenKH NVARCHAR(100),
		LEVEL INT,
		ParentKhachHangID UNIQUEIDENTIFIER,
		Thang INT,
		Nam INT,
		DoanhSoBASE MONEY,
		DoanhSoBotBa MONEY,
		DoanhSoSon MONEY,
		ThuTheoCT MONEY,
		DaThu MONEY,
		GhiChu NVARCHAR(max),
		ThoiDiemLapMay DATETIME,
		LoaiMay INT,
		TenLoaiMay VARCHAR(100)
	)

	INSERT INTO #tblMayPhaMauReport(
	ID,
	TenTrungTam, 
	KhachHangID, 
	MaKH, 
	TenKH, 
	LEVEL, 
	ParentKhachHangID, 
	Thang, 
	Nam, 
	GhiChu,
	ThoiDiemLapMay,
	LoaiMay,
	TenLoaiMay
	)
	SELECT 
		KH.ID,
		@TrungTamName AS TenTrungTam,
		KH.KhachHangID,
		ao.MaKH as MaKH, 
        ao.TenKH as TenKH,
        ao.LEVEL,
        ao.ParentID,
        @Month,
        @Year,
        KH.GhiChu,
        KH.ThoiDiemLapMay,
        KH.LoaiMay,
        CASE WHEN KH.LoaiMay = 0 THEN N'Tự động (xuất xứ Châu Âu)' ELSE 
        CASE WHEN KH.LoaiMay = 1 THEN N'Tự động (xuất xứ Ấn Độ/Trung Quốc)' ELSE 
        CASE WHEN KH.LoaiMay = 2 THEN N'Bán tự động' END END END
FROM	KhachHangLapMayPhaMau KH 
INNER JOIN vwAllCustomers ao ON KH.KhachHangID = ao.KhachHangID
WHERE KH.IsActive = 1

	DECLARE @khachHangDuThuongID UNIQUEIDENTIFIER
	DECLARE @khachHangID UNIQUEIDENTIFIER
	DECLARE @startDate DATETIME

	DECLARE @DoanhSoBASE MONEY
	DECLARE @DoanhSoBotBa MONEY
	DECLARE @DoanhSoSon MONEY
	DECLARE @DaThu MONEY

	DECLARE crsKH CURSOR FOR SELECT ID, KhachHangID, ThoiDiemLapMay FROM #tblMayPhaMauReport
	OPEN crsKH
	FETCH NEXT FROM crsKH
	INTO @khachHangDuThuongID, @khachHangID, @startDate

	WHILE @@FETCH_STATUS = 0
	BEGIN
		SELECT @DoanhSoBASE = SUM(vfp.Amount + vfp.VATAmount)
		FROM [ATAHANOI]..View_VoucherForPayable vfp
		LEFT JOIN [ATAHANOI]..InventoryItem ii ON vfp.InventoryItemID = ii.InventoryItemID
		LEFT JOIN [ATAHANOI]..InventoryItemCategory iic ON  ii.InventoryCategoryID = iic.InventoryCategoryID

		WHERE
				(vfp.CreditAccount LIKE '511%' and vfp.DebitAccount LIKE '131%') 
		        AND (vfp.Posted=1 OR vfp.Posted IS NULL)
		        AND (vfp.PostedDate BETWEEN @startDate AND @LastDate)
				AND (ISNULL(iic.InventoryCategoryCode, '') LIKE '%BS%')
				AND vfp.AccountingObjectID = @khachHangID
		GROUP BY vfp.AccountingObjectID

		SELECT @DoanhSoBotBa = SUM(vfp.Amount + vfp.VATAmount)
		FROM [ATAHANOI]..View_VoucherForPayable vfp
		LEFT JOIN [ATAHANOI]..InventoryItem ii ON vfp.InventoryItemID = ii.InventoryItemID
		LEFT JOIN [ATAHANOI]..InventoryItemCategory iic ON  ii.InventoryCategoryID = iic.InventoryCategoryID
		WHERE
				(vfp.CreditAccount LIKE '511%' and vfp.DebitAccount LIKE '131%') 
		        AND (vfp.Posted=1 OR vfp.Posted IS NULL)

		        AND (vfp.PostedDate BETWEEN @startDate AND @LastDate)
				AND (ISNULL(iic.InventoryCategoryCode, '') LIKE '%BT%')
				AND vfp.AccountingObjectID = @khachHangID
		GROUP BY vfp.AccountingObjectID

		SELECT @DoanhSoSon = SUM(vfp.Amount + vfp.VATAmount)
		FROM [ATAHANOI]..View_VoucherForPayable vfp
		LEFT JOIN [ATAHANOI]..InventoryItem ii ON vfp.InventoryItemID = ii.InventoryItemID
		LEFT JOIN [ATAHANOI]..InventoryItemCategory iic ON  ii.InventoryCategoryID = iic.InventoryCategoryID
		WHERE
				(vfp.CreditAccount LIKE '511%' and vfp.DebitAccount LIKE '131%') 
		        AND (vfp.Posted=1 OR vfp.Posted IS NULL)
		        AND (vfp.PostedDate BETWEEN @startDate AND @LastDate)
				AND ( (ISNULL(iic.InventoryCategoryCode, '') LIKE '%SL%') OR (ISNULL(iic.InventoryCategoryCode, '') LIKE '%SP%') )
				AND vfp.AccountingObjectID = @khachHangID
		GROUP BY vfp.AccountingObjectID
		
		SELECT @DaThu = SUM(DebitAmount)
		FROM ATAHANOI.dbo.GeneralLedger gl
		WHERE gl.AccountingObjectID = @khachHangID
		AND MONTH(PostedDate) = @Month AND YEAR(PostedDate) = @Year
		AND CorrespondingAccountNumber LIKE '13683%'
		
		UPDATE #tblMayPhaMauReport SET 
		DoanhSoBASE = @DoanhSoBASE, 
		DoanhSoBotBa = @DoanhSoBotBa, 
		DoanhSoSon = @DoanhSoSon,
		ThuTheoCT = ISNULL(@DoanhSoBASE, 0) + ISNULL(@DoanhSoBotBa, 0) + ISNULL(@DoanhSoSon, 0) * 3 / 100,
		DaThu = ISNULL(@DaThu, 0)
		WHERE ID = @khachHangDuThuongID

		FETCH NEXT FROM crsKH
		INTO @khachHangDuThuongID, @khachHangID, @startDate
	END

	CLOSE crsKH;
	DEALLOCATE crsKH;

SELECT * FROM #tblMayPhaMauReport
ORDER BY TenKH

DROP TABLE #tblMayPhaMauReport
-- sprMayPhaMauReport '4EB226E3-FB42-4AB4-B82E-7021D2322B40', 7, 2010
END




GO


